package com.yyqx.web_maven.dao;

import com.yyqx.web_maven.entity.Users;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UsersDao {
    public List<Users> getList()throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库链接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_maven?useUnicode=true&characterEncoding=utf8",
                "root","");
        //准备pstmt对象
        PreparedStatement pstmt = conn.prepareStatement("select * from users");

        //执行sql语句
        ResultSet rs = pstmt.executeQuery();

        //循环获取数据
        List<Users> list = new ArrayList<Users>();
        while (rs.next()){
            Users u = new Users();
            u.setId(rs.getInt("id"));
            u.setUsername(rs.getString("username"));
            u.setAge(rs.getInt("age"));
            u.setBirth(rs.getTimestamp("birth"));
            list.add(u);

        }


        rs.close();
        conn.close();
        return list;
    }
    public void save(Users user) {

        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据的连接
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test_maven?useUnicode=true&characterEncoding=utf8",
                    "root","");
            //查询user表的数据
            String sql = "select  * from users";
            //向user表增加数据
            String sql1 = "insert into users(id,name,age,school) value(6,'bp',23,'yg')";

            //删除user表的数据
            String sql2 = "delete from users where id=1";
            //修改user表的数据
            String sql3 = "update users set age = 21 where id = 5";

            //准备pstmt对象
            PreparedStatement pstmt = conn.prepareStatement(sql);

            System.out.println("=========增=========");
            System.out.println();
            pstmt.executeUpdate(sql1);


            System.out.println("=========删=========");
            System.out.println();
            pstmt.executeUpdate(sql2);


            System.out.println("=========改=========");
            System.out.println();
            pstmt.executeUpdate(sql3);


            //执行mysql语句
            ResultSet res = pstmt.executeQuery();
            System.out.println("=======users表原数据========");
            //查询结果
            while(res.next()){
                System.out.print(res.getInt("id")+"\t");
                System.out.print(res.getString("name")+"\t");
                System.out.print(res.getInt("age")+"\t");
                System.out.print(res.getString("school")+"\t");
                System.out.println();
            }
            conn.close();
        } catch (Exception e){
            e.printStackTrace();
        }

    }
}
